A database, such as a relational database, is an organized collection of data. Such databases commonly organize a given collection of data into objects that may be expressed using, e.g., tables, indexes, columns, and rows. Each object in a database, such as a relational database, may be identified using an identifier that is one attribute of, and unique to a given object. Such identifiers may be referred to, for example, as object identifiers (IDs), record IDs, unique IDs or simply IDs. In certain situations, it is desirable to retain different versions of an object with a given (unique) ID. For instance, retaining different versions of database objects may be desirable in order to keep track of historical information that is included in different versions of each given object.
As an example, a relational database may be used to maintain personnel records for a company, where information for each employee of the company may be included in different versions of a database object that each have (share) a respective unique ID, where each unique object ID corresponds with a respective employee. In such an implementation, each piece of information in the different versions of a given employee's database object (or record) may be referred to as an attribute of that record (object). Thus, the different versions of a given employee's database record (object) may be used to keep track of historical information for that employee, such as changes in compensation, changes in work assignment, etc. Of course, such an implementation is given by way of example and many other situations and implementations are possible.
In approaches that implement database objects that have different versions (with the same ID) persisted in a database, it also often desirable that at least a subset of an object's attributes be unique (unique attributes) across different versioned database objects, such as objects with different unique IDs (e.g., database objects for different employees), but not for different versions of the same object (e.g., different versions of a database object for a specific employee). In such situations, a comparison of a subset of unique attributes between different versioned database objects in a given database should indicate that each of those unique attributes is not repeated for objects with different IDs.
Such approaches allow the same set of attributes to be shared by different versions of an object having the same ID. Thus, from a DB perspective, when persisting (storing) different versions of a given object (with the same ID) within a relational DB table (along with other versioned database objects, e.g., of the same type, such as employee records, with different IDs), such unique constraints may not be enforced for different versions of an object with the same unique ID and, thus, those attributes can be repeated by different versions of the same object. For example, each employee may be assigned an employee number that is required to be unique for each employee record (with different IDs). However, the employee number of a given employee may be repeated in different versions of that employee's record (database object) in the relational database (with the same ID).
A common situation in databases that store (persist) such versioned database objects, may occur when two different threads (e.g., from an application layer or separate application layers) try to create new versions of different versioned database objects (with different IDs) at the same time. In an example, it is assumed that a NAME attribute of each versioned database object in a given database should be unique across different objects, while the value of the NAME attribute may be the same for different versions of the same object. In this example, it is also assumed that both of the threads are trying to insert different versioned database objects (e.g., representing respective servers in a computing network with different IDs) with a NAME attribute of “Elvis” at substantially the same time.
In this situation, in current implementations, each thread would first read a database table that is used to persist such versioned database objects and, as a result, each thread could determine that there is no committed record with a NAME attribute of “Elvis”, e.g., if no such record had been created before the two threads attempt to create their associated records. In this situation, after reading the database table, both threads would insert their respective new records that violate the uniqueness of the NAME attribute, as a result of two different versioned database objects (with different IDs) with a name attribute of “Elvis” being created.
In certain situations, it may be desirable not only that unique attributes of records stored in a relational database be unique across different versioned database objects (with different IDs), but also that the unique attributes of different versions of the same object remain unchanged (are immutable across versions), though other attributes (not requiring uniqueness) may change across versions. In such situations, the unique attributes that are to remain unchanged across different versions of the same object may be considered to operate as an additional (or extended) unique ID for their respective object.
In order to enforce such uniqueness constraints, current approaches use techniques where an entire database table is locked when any thread attempts to insert, update or delete a record (object) in the table, and each such thread is handled in a serial (e.g., first-in-first-out) fashion. However, such approaches have undesirable outcomes, as locking an entire database table severely reduces concurrent (parallel) data access and increases operation latency (due to serial processing). Accordingly, such approaches severely limit performance of applications and/or databases in which versioned database objects and uniqueness constraints are implemented.